package com.xhd.Dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import com.xhd.Dao.SalerDao;
import com.xhd.vo.Fruits;
import com.xhd.vo.Order;
import com.xhd.vo.Yixiang;
import com.xhd.vo.Tuijian;

public class SalerDaoImpl implements SalerDao {
	public static final String URL="jdbc:mysql://localhost:3306/xhd";
	public static final String USER="root";
	public static final String PWD="root";
	@Override
	public boolean loginCheck(String pwd) throws SQLException {
		ResultSet rs=null;
		PreparedStatement ps=null;
		Connection con=null;
		//System.out.println("yeah");
		try{
			Class.forName("com.mysql.cj.jdbc.Driver");
			con =DriverManager.getConnection(URL,USER,PWD);
			String sql="select pwd from saler where pwd=?";
			ps=con.prepareStatement(sql);
			ps.setString(1, pwd);
			rs=ps.executeQuery();
			//System.out.println("yeah");
			if (rs.next()) {
				
					return true;
				}
				else {
					return false;
				}
			
		}catch (Exception e) {
			// TODO: handle exception
		}finally {
			if (null!=rs)
				rs.close();
			if (null!=ps)
				ps.close();
			if (null!=con)
				con.close();
		}
		return false;
	}
	@Override
	public void updatePwd(String pwd) throws SQLException {
		ResultSet rs=null;
		PreparedStatement ps=null;
		Connection con=null;
		
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			con =DriverManager.getConnection(URL,USER,PWD);
			String sql="update saler set pwd=?";
			ps=con.prepareStatement(sql);
			ps.setString(1, pwd);
			ps.executeUpdate();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if (null!=rs)
				rs.close();
			if (null!=ps)
				ps.close();
			if (null!=con)
				con.close();
		}
		
		
		
	}
	@Override
	public void addFruit(Fruits fruit)throws SQLException {
		ResultSet rs=null;
		PreparedStatement ps=null;
		Connection con=null;
		
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			con =DriverManager.getConnection(URL,USER,PWD);
			String sql="insert into fruit values (?,?,?,?,?,?)";
			ps=con.prepareStatement(sql);
			ps.setString(1, fruit.getFruit_name());
			ps.setString(2, fruit.getFruit_describe());
			ps.setString(3, fruit.getFruit_picture());
			ps.setString(4, fruit.getFruit_price());
			ps.setString(5, fruit.getFruit_state());
			ps.setString(6, fruit.getFruit_stock());
			ps.executeUpdate();
			
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if (null!=rs)
				rs.close();
			if (null!=ps)
				ps.close();
			if (null!=con)
				con.close();
		}

		
	}
	
	@Override
	public List<Fruits> haveFruitOrNot(String state) throws SQLException {
		ResultSet rs=null;
		PreparedStatement ps=null;
		Connection con=null;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			con =DriverManager.getConnection(URL,USER,PWD);
			String sql="select `name`,`describe`,picture,price,stock from fruit where state=?";
			ps=con.prepareStatement(sql);
			ps.setString(1, state);
			ps.executeQuery();
			rs=ps.executeQuery();
			Fruits fruit=null;
			List<Fruits> fruitlist=new ArrayList<Fruits>();
			while(rs.next()) {
				fruit=new Fruits(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),state,rs.getString(5));
				fruitlist.add(fruit);
			}
			return fruitlist;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if (null!=rs)
				rs.close();
			if (null!=ps)
				ps.close();
			if (null!=con)
				con.close();
		}
		return null;
	}
	@Override
	public void addOrder(Order order) throws SQLException {
		ResultSet rs=null;
		PreparedStatement ps=null;
		Connection con=null;
		
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			con =DriverManager.getConnection(URL,USER,PWD);
			String sql="insert into `order` values (?,?,?,?,?,?)";
			ps=con.prepareStatement(sql);
			ps.setString(1, order.getOrder_buyer());
			ps.setString(2, order.getOrder_phone());
			ps.setString(3, order.getOrder_address());
			ps.setString(4, order.getOrder_fruit());
			ps.setString(5, order.getOrder_state());
			ps.setString(6, order.getOrder_sum());
			ps.executeUpdate();
			
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if (null!=rs)
				rs.close();
			if (null!=ps)
				ps.close();
			if (null!=con)
				con.close();
		}
		
	}
	@Override
	public List<Order> showOrder() throws SQLException {
		ResultSet rs=null;
		PreparedStatement ps=null;
		Connection con=null;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			con =DriverManager.getConnection(URL,USER,PWD);
			String sql="select * from `order`";
			ps=con.prepareStatement(sql);
			ps.executeQuery();
			rs=ps.executeQuery();
			Order order=null;
			List<Order> orderlist=new ArrayList<Order>();
			while(rs.next()) {
				order=new Order(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5));
				orderlist.add(order);
			}
			return orderlist;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if (null!=rs)
				rs.close();
			if (null!=ps)
				ps.close();
			if (null!=con)
				con.close();
		}
		return null;
	}
	@Override
	public void changeFruitState(String differentState,String fruit_name) throws SQLException {
		ResultSet rs=null;
		PreparedStatement ps=null;
		Connection con=null;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			con =DriverManager.getConnection(URL,USER,PWD);
			if (differentState.equals("freeze")) {
				String sql="update `fruit` set state=? where name=?";
				ps=con.prepareStatement(sql);
				ps.setString(1, "被冻结");
				ps.setString(2, fruit_name);
				ps.executeUpdate();
			}
			else if (differentState.equals("salesuccess")) {
				String sql="update `fruit` set state=? where name=?";
				ps=con.prepareStatement(sql);
				ps.setString(1, "已售出");
				ps.setString(2, fruit_name);
				ps.executeUpdate();
			}
			else if (differentState.equals("salefail")) {
				String sql="update `fruit` set state=? where name=?";
				ps=con.prepareStatement(sql);
				ps.setString(1, "待售");
				ps.setString(2, fruit_name);
				ps.executeUpdate();
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if (null!=rs)
				rs.close();
			if (null!=ps)
				ps.close();
			if (null!=con)
				con.close();
		}
	}
	@Override
	public void changeOrderState(String differentState, String fruit_name,String order_buyer) throws SQLException {
		ResultSet rs=null;
		PreparedStatement ps=null;
		Connection con=null;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			con =DriverManager.getConnection(URL,USER,PWD);
			if (differentState.equals("ordersuccess")) {
				String sql="update `order` set order_state=? where order_fruit=? and order_buyer=?";
				ps=con.prepareStatement(sql);
				ps.setString(1, "交易成功");
				ps.setString(2, fruit_name);
				ps.setString(3, order_buyer);
				ps.executeUpdate();
			}
			else if (differentState.equals("orderfail")) {
				if (order_buyer.equals("*")) {
					String sql="update `order` set order_state=? where order_fruit=? and order_state=?";
					ps=con.prepareStatement(sql);
					ps.setString(1, "待处理");
					ps.setString(2, fruit_name);
					ps.setString(3, "非意向订单");
					ps.executeUpdate();
				}
				else {
					String sql="update `order` set order_state=? where order_fruit=? and order_buyer=?";
					ps=con.prepareStatement(sql);
					ps.setString(1, "交易失败,期待下次交易~");
					ps.setString(2, fruit_name);
					ps.setString(3, order_buyer);
					ps.executeUpdate();
				}
			}
			else if (differentState.equals("haveintention")) {
				if (order_buyer.equals("*")) {
					String sql="update `order` set order_state=? where order_fruit=? and order_state=?";
					ps=con.prepareStatement(sql);
					ps.setString(1, "非意向订单");
					ps.setString(2, fruit_name);
					ps.setString(3, "待处理");
					ps.executeUpdate();
				}
				else {
					String sql="update `order` set order_state=? where order_fruit=? and order_buyer=?";
					ps=con.prepareStatement(sql);
					ps.setString(1, "意向订单");
					ps.setString(2, fruit_name);
					ps.setString(3, order_buyer);
					ps.executeUpdate();
				}
			}
			else if (differentState.equals("intentionfail")) {
				String sql="update `order` set order_state=? where order_fruit=? and order_buyer=?";
				ps=con.prepareStatement(sql);
				ps.setString(1, "交易取消");
				ps.setString(2, fruit_name);
				ps.setString(3, order_buyer);
				ps.executeUpdate();
			}
			else if(differentState.equals("out")) {
				String sql="update `order` set order_state=? where order_fruit=? and order_buyer=?";
				ps=con.prepareStatement(sql);
				ps.setString(1, "需求数大于存货，订单失效");
				ps.setString(2, fruit_name);
				ps.setString(3, order_buyer);
				ps.executeUpdate();
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if (null!=rs)
				rs.close();
			if (null!=ps)
				ps.close();
			if (null!=con)
				con.close();
		}
	}
	@Override
	public void addFruitYixiang(Yixiang yixiang) throws SQLException {
		ResultSet rs=null;
		PreparedStatement ps=null;
		Connection con=null;
		
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			con =DriverManager.getConnection(URL,USER,PWD);
			String sql="insert into `yixiang` values (?,?,?,?,?,?,?,?)";
			ps=con.prepareStatement(sql);
			ps.setString(1, yixiang.getName());
			ps.setString(2, yixiang.getPhonenum());
			ps.setString(3, yixiang.getFruit());
			ps.setString(4, yixiang.getAddress());
			ps.setString(5, yixiang.getJiaoyifangshi());
			ps.setString(6, yixiang.getSheng());
			ps.setString(7, yixiang.getShi());
			ps.setString(8, yixiang.getQu());
			ps.executeUpdate();
				
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if (null!=rs)
				rs.close();
			if (null!=ps)
				ps.close();
			if (null!=con)
				con.close();
		}
	}
	@Override
	public List<Yixiang> showYixiang() throws SQLException {
		ResultSet rs=null;
		PreparedStatement ps=null;
		Connection con=null;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			con =DriverManager.getConnection(URL,USER,PWD);
			String sql="select * from `yixiang`";
			ps=con.prepareStatement(sql);
			ps.executeQuery();
			rs=ps.executeQuery();
			Yixiang yixiang=null;
			List<Yixiang> yixianglist=new ArrayList<Yixiang>();
			while(rs.next()) {
				yixiang=new Yixiang(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8));				yixianglist.add(yixiang);
			}
			return yixianglist;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if (null!=rs)
				rs.close();
			if (null!=ps)
				ps.close();
			if (null!=con)
				con.close();
		}
		return null;
	}
	
	@Override
	public List<Fruits> serchFruit(String name) throws SQLException {
		ResultSet rs=null;
		PreparedStatement ps=null;
		Connection con=null;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			con =DriverManager.getConnection(URL,USER,PWD);
			String sql="select `name`,`describe`,picture,price,state from fruit where `describe` like \"%\"?\"%\" or `name` like \"%\"?\"%\" ";
			String sql2="select `name`,`describe`,picture,price,state from fruit";
			if(name==null || name=="") {
				//System.out.println("sql2");
				ps=con.prepareStatement(sql2);
				ps.executeQuery();
				rs=ps.executeQuery();
			}
			else {
				//System.out.println("sql1");
				ps=con.prepareStatement(sql);
				ps.setString(1, name);
				ps.setString(2, name);
				ps.executeQuery();
				rs=ps.executeQuery();
			}
				Fruits fruit=null;
				List<Fruits> fruitlist=new ArrayList<Fruits>();
				while(rs.next()) {
					fruit=new Fruits(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5));
					fruitlist.add(fruit);
				}
				return fruitlist;
				
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if (null!=rs)
				rs.close();
			if (null!=ps)
				ps.close();
			if (null!=con)
				con.close();
		}
		return null;
	}

	@Override
	public void addTJfruit(Tuijian tuijian) throws SQLException {
		ResultSet rs=null;
		PreparedStatement ps=null;
		Connection con=null;
		
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			con =DriverManager.getConnection(URL,USER,PWD);
			String sql="insert into `tuijian` values (?,?,?)";
			ps=con.prepareStatement(sql);
			ps.setString(1, tuijian.getTuijian());
			ps.setString(2, tuijian.getYuefen());
			ps.setString(3, tuijian.getTjly());
			ps.executeUpdate();
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if (null!=rs)
				rs.close();
			if (null!=ps)
				ps.close();
			if (null!=con)
				con.close();
		}
		
	}
	
	@Override
	public List<Tuijian> showTuijian() throws SQLException {
		ResultSet rs=null;
		PreparedStatement ps=null;
		Connection con=null;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			con =DriverManager.getConnection(URL,USER,PWD);
			String sql="select * from `tuijian` ";
			ps=con.prepareStatement(sql);
			ps.executeQuery();
			rs=ps.executeQuery();
			Tuijian tuijian=null;
			List<Tuijian> tuijianlist=new ArrayList<Tuijian>();
			while(rs.next()) {
				tuijian=new Tuijian(rs.getString(1),rs.getString(2),rs.getString(3));
				tuijianlist.add(tuijian);
			}
			return tuijianlist;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if (null!=rs)
				rs.close();
			if (null!=ps)
				ps.close();
			if (null!=con)
				con.close();
		}
		return null;
	}
	@Override
	public void delTJfruit(String tuijian) throws SQLException {
		ResultSet rs=null;
		PreparedStatement ps=null;
		Connection con=null;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			con =DriverManager.getConnection(URL,USER,PWD);
			String sql="delete from tuijian where TJfruit=?";
			ps=con.prepareStatement(sql);
			ps.setString(1, tuijian);
			ps.executeUpdate();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if (null!=rs)
				rs.close();
			if (null!=ps)
				ps.close();
			if (null!=con)
				con.close();
		}
		// TODO Auto-generated method stub
		
	}
	@Override
	public List<Tuijian> findby(String str) throws SQLException {
		//System.out.println(str);
		ResultSet rs=null;
		PreparedStatement ps=null;
		Connection con=null;
		List<Tuijian> tuijianlist=new LinkedList<Tuijian>();
		String sql="select * from tuijian where TJfruit like ? ";

		
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			con =DriverManager.getConnection(URL,USER,PWD);
			ps=con.prepareStatement(sql);
			ps.setString(1, str);
			rs=ps.executeQuery();
			while(rs.next()) {
				Tuijian tuijian=new Tuijian();
				tuijian.setTuijian(rs.getString("TJfruit"));
				tuijian.setYuefen(rs.getString("yuefen"));
				tuijian.setTjly(rs.getString("tjly"));
				tuijianlist.add(tuijian);
			}
			return tuijianlist;
	}catch (ClassNotFoundException e) {
		throw new RuntimeException();
	}
}
	@Override
	public List<Order> showOrderByFruit(String fruit_name) throws SQLException {
		ResultSet rs=null;
		PreparedStatement ps=null;
		Connection con=null;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			con =DriverManager.getConnection(URL,USER,PWD);
			String sql="SELECT * FROM `order` where order_fruit=?";
			ps=con.prepareStatement(sql);
			ps.setString(1, fruit_name);
			ps.executeQuery();
			rs=ps.executeQuery();
			Order order=null;
			List<Order> orderlist=new ArrayList<Order>();
			while(rs.next()) {
				order=new Order(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6));
				orderlist.add(order);
			}
			return orderlist;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if (null!=rs)
				rs.close();
			if (null!=ps)
				ps.close();
			if (null!=con)
				con.close();
		}
		return null;
	}
	
	@Override
	public String FruitExist(String name) throws SQLException {
		ResultSet rs=null;
		PreparedStatement ps=null;
		Connection con=null;
		String fruitname = "";
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			con =DriverManager.getConnection(URL,USER,PWD);
			
			String sql="select name from fruit where name=?";
			ps=con.prepareStatement(sql);
			ps.setString(1,name);
	        rs= ps.executeQuery();
	        	if(rs.next()){
	        		fruitname=rs.getString(name);
	        		
  }}catch (ClassNotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally {
		if (null!=rs)
			rs.close();
		if (null!=ps)
			ps.close();
		if (null!=con)
			con.close();
	}
		return fruitname;
}
	@Override
	public void FruitSoldOut(String fruit_name, String sum, String stock) throws SQLException {
		ResultSet rs=null;
		PreparedStatement ps=null;
		Connection con=null;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			con =DriverManager.getConnection(URL,USER,PWD);
			int stockNum = Integer.parseInt(stock);
			int sumNum = Integer.parseInt(sum);
			int remain=stockNum-sumNum;
			if (remain>0) {
				String sql="update `fruit` set stock=?,state=? where name=?";
				ps=con.prepareStatement(sql);
				ps.setString(1, Integer.toString(remain));
				boolean flag=haveOrderOrNot("意向订单");
				if (!flag) {
					ps.setString(2, "待售");
				}else {
					ps.setString(2, "被冻结");
				}
				ps.setString(3, fruit_name);
				ps.executeUpdate();

			}else if (remain==0) {
				String sql="update `fruit` set stock=?,state=? where name=?";
				ps=con.prepareStatement(sql);
				ps.setString(1, Integer.toString(remain));
				ps.setString(2, "已售出");
				ps.setString(3, fruit_name);
				ps.executeUpdate();
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if (null!=rs)
				rs.close();
			if (null!=ps)
				ps.close();
			if (null!=con)
				con.close();
		}
	}
	@Override
	public boolean haveOrderOrNot(String state) throws SQLException {
		ResultSet rs=null;
		PreparedStatement ps=null;
		Connection con=null;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			con =DriverManager.getConnection(URL,USER,PWD);
			String sql="select * from `order` where order_state=?";
			ps=con.prepareStatement(sql);
			ps.setString(1, state);
			rs=ps.executeQuery();
			//System.out.println("yeah");
			if (rs.next()) {
					return true;
				}
				else {
					return false;
				}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if (null!=rs)
				rs.close();
			if (null!=ps)
				ps.close();
			if (null!=con)
				con.close();
		}
		
		return false;
	}
}
	
	

